
/*   RolGPS Project
 * 
 *   Copyright (C) 2009 Pablo Alejandro Costesich <rhfixer@gmail.com>
 *
 *   This program is free software: you can redistribute it and/or modify
 *   it under the terms of the GNU General Public License as published by
 *   the Free Software Foundation, either version 3 of the License, or
 *   (at your option) any later version.
 *
 *   This program is distributed in the hope that it will be useful,
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *   GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
 */
package org.rolgps.libedb.engine.sqlite3.zentus;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.rolgps.libedb.container.DataResult;
import org.rolgps.libedb.standard.Database;
import org.rolgps.libedb.exception.QueryException;
import org.rolgps.libedb.factory.DatabaseFactory;

/**
 *
 * @author xeph
 */
public class EngineCore implements Database {

    private static final String CONTYPE = Database.ONLINE;
    private String username = "root";
    private String databaseFile = "temp.db";
    private String driverManager = "org.sqlite.JDBC";
    private String connectString = "jdbc:sqlite:";
    private Connection database = null;

    public EngineCore(String file) {
        if (file.equals("") || file == null) {
            databaseFile = ":memory:";
        }
        databaseFile = file;
    }

    public EngineCore() {
        databaseFile = ":memory:";
    }

    public void setConnectionUser(String user) {
        this.username = user;
    }

    public void setConnectionAddr(String addr) {
        databaseFile = addr;
    }

    public void setConnectionPasswd(String passwd) {
        return;
    }

    public String getConnectionType() {
        return CONTYPE;
    }

    public String getConnectionUser() {
        return this.username;
    }

    public String getConnectionAddr() {
        File result = new File(databaseFile);
        return result.getAbsolutePath();
    }

    public DataResult executeStatement(String sqlStatement) throws QueryException {
        DataResult resultado = new DataResult();
        //sqlStatement = escapeField(sqlStatement);
        try {
            this.openConnection();
            Statement statement = database.createStatement();
            if (sqlStatement.toLowerCase().startsWith("select")) {
                throw new QueryException("You shouldn't use this function to query the database.");
            } else {
                int resultNumber = statement.executeUpdate(sqlStatement);
                resultado.addInt(resultNumber);
            }

            this.closeConnection();
        } catch (SQLException e) {
            throw new QueryException(e.getSQLState() + "\n" + e.getMessage());
        }
        System.gc();
        return resultado;
    //throw new UnsupportedOperationException("Not supported yet.");
    }

    public String escapeString(String toScape) {
        return toScape.replaceAll("\'", "\'\'");
    }

    private void openConnection() throws SQLException {
        try {
            Class.forName(getDriverManager());
            database = DriverManager.getConnection(this.getConnectString() + this.getConnectionAddr());
            database.setAutoCommit(false);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(EngineCore.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void closeConnection() {
        try {
            if (!database.getAutoCommit()) {
                database.commit();
            }
            database.clearWarnings();
            database.close();
        } catch (SQLException ex) {
            Logger.getLogger(EngineCore.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public String getDriverManager() {
        return driverManager;
    }

    public void setDriverManager(String driverManager) {
        this.driverManager = driverManager;
    }

    public String getConnectString() {
        return connectString;
    }

    public void setConnectString(String connectString) {
        this.connectString = connectString;
    }
    
    /*
     * Well, this code just... executes the statement.
     * Give it the query, the data and it will give you love and a nice
     * DataResult array with the status for each query.
     * 
     * Frankly, I don't know what the rest of the code in this file does. I was 
     * studying Algebra while sleeping after a party where I got high on 
     * logarithms (man, those are REALLY good... You should try Number Theory!).
     * 
     * PS: Yeah, I study while sleeping. You know you spend 22 years of your
     * life sleeping, right? That's time you could spend doing something useful,
     * such as compiling OpenOffice.org...
     * 
     * ...Though you would need more than just 22 years. 
     * 
     */

    public DataResult[] executeStatement(String sqlStatement, String[]... fields) throws QueryException {

        DataResult[] resultado = new DataResult[0];
        //sqlStatement = escapeField(sqlStatement);
        try {
            this.openConnection();
            Statement statement = database.createStatement();
            if (sqlStatement.toLowerCase().startsWith("select")) {
                throw new QueryException("You shouldn't use this function to query the database.");
            } else {
                for (String[] field : fields) {
                    statement.addBatch(escapeQuery(sqlStatement, field));
                }
                int[] res = statement.executeBatch();
                resultado = new DataResult[res.length];
                this.closeConnection();
                for (int x = 0; x < res.length; x++) {
                    resultado[x] = new DataResult();
                    resultado[x].addInt(res[x]);
                }
            }

            this.closeConnection();
        } catch (SQLException e) {
            throw new QueryException(e.getSQLState() + "\n" + e.getMessage());
        }
        System.gc();
        return resultado;
    }

    private String escapeQuery(String query, String[] values) throws SQLException {
        for (int x = 0; x < values.length; x++) {
            values[x] = escapeString(values[x]);
        }
        int index = 0;
        int arrayIndex = 0;
        while ((index = query.indexOf("?", index)) < query.length()) {
            if (index < 0) {
                break;
            } else {
                query = query.substring(0, index) + values[arrayIndex] + query.substring(index + values[arrayIndex].length());
                index = index + values[arrayIndex].length();
                arrayIndex++;
            }
        }
        return query;
    }

    /*
     * Returns a DataResult array with all the results given by the query.
     * The fields array should have the same dimension as fields to be escaped.
     * 
     * It checks for a select query, because the behaviour is different when
     * updating or deleting. Then it escapes que query, executes it, gets the
     * metadata so it can grab the column count and iterates over the ResultSet.
     * Using the column number it gets every field and builds a new DataResult
     * (strings only), which gets loaded into an ArrayList to be returned as an
     * array by casting it as a DataResult[].
     * 
     * If it doesn't start by select then it returns the query status number.
     * 
     * I don't expect you to understand this.
     */
    public DataResult[] executeQuery(String sqlStatement, String[] fields) throws QueryException {
        DataResult[] result = new DataResult[0];
        int colNumber = 0;
        try {
            openConnection();

            PreparedStatement prepStatement = database.prepareStatement(sqlStatement);
            if (sqlStatement.toLowerCase().startsWith("select") && fields.length == 1) {
                int fieldIndex = 1;
                for (String fieldValue : fields) {
                    prepStatement.setString(fieldIndex, fieldValue);
                    fieldIndex++;
                }
                ResultSet selectResult = prepStatement.executeQuery();
                colNumber = selectResult.getMetaData().getColumnCount();
                ArrayList<DataResult> rows = new ArrayList(); 
                DataResult columns = new DataResult();
                while (selectResult.next()) {
                    for (int readIteration = 0; readIteration < colNumber; readIteration++) {
                        if (selectResult.getString(readIteration + 1) == null) {
                            columns.addString(""); 
                        } else {
                            columns.addString(selectResult.getString(readIteration + 1));
                        }
                    }
                    rows.add(columns);
                    result = (DataResult[]) rows.toArray();
                }
            } else if (!sqlStatement.toLowerCase().startsWith("select")) {

                int fieldIndex = 1;
                for (String fieldValue : fields) {
                    prepStatement.setString(fieldIndex, fieldValue);
                    fieldIndex++;
                }
                prepStatement.addBatch();

                int[] res = prepStatement.executeBatch();
                result = new DataResult[res.length];
                this.closeConnection();
                for (int x = 0; x < res.length; x++) {
                    result[x] = new DataResult();
                    result[x].addInt(res[x]);
                }
            } else {
                throw new QueryException();
            }
        } catch (SQLException e) {
            throw new QueryException(e.getSQLState() + "\n" + e.getMessage());
        }
        System.gc();
        return result;
    }

    
    
    public DataResult[] executeQuery(String sqlStatement) throws QueryException {
        DataResult[] result = new DataResult[0];
        int colNumber = 0;
        try {
            openConnection();

            if (sqlStatement.toLowerCase().startsWith("select")) {
                Statement queryStatement = database.createStatement();
                ResultSet selectResult = queryStatement.executeQuery(sqlStatement);
                colNumber = selectResult.getMetaData().getColumnCount();
                ArrayList<DataResult> rows = new ArrayList(); 
                DataResult columns = new DataResult();
                while (selectResult.next()) {
                    for (int readIteration = 0; readIteration < colNumber; readIteration++) {
                        if (selectResult.getString(readIteration + 1) == null) {
                            columns.addString(""); 
                        } else {
                            columns.addString(selectResult.getString(readIteration + 1));
                        }
                    }
                    rows.add(columns);
                    result = (DataResult[]) rows.toArray();
                }
            } else {
                throw new QueryException();
            }
        } catch (SQLException e) {
            throw new QueryException(e.getSQLState() + "\n" + e.getMessage());
        }
        System.gc();
        return result;
    }

    /*
     * This creates a DataResult with the result of the query to be scaped.
     * The third argument, int[] dataTypes is the result builder: it creates
     * the correct type for the DataResult, so if you want an integer you will
     * get an integer (unless you try to parse an alphanumeric string, in which
     * case this _may_ explode and destroy the entire building).
     * 
     * If the query starts with SELECT, then it will return an array of
     * DataResult with the specified structure for each DataResult.
     * The Switch is the Little Thing where the magic comes from.
     */
    public DataResult[] executeQuery(String skel, String[] fields, int[] dataTypes) throws QueryException {
        DataResult[] result = new DataResult[0];
        int colNumber = 0;
        try {
            openConnection();

            PreparedStatement prepStatement = database.prepareStatement(skel);
            if (skel.toLowerCase().startsWith("select") && fields.length == 1) {
                int fieldIndex = 1;
                for (String fieldValue : fields) {
                    prepStatement.setString(fieldIndex, fieldValue);
                    fieldIndex++;
                }
                ResultSet selectResult = prepStatement.executeQuery(); //Ehrm... Executes the query. Funny, isn't it?
                selectResult.setFetchSize(100); //This *should* help when fetching large datasets.
                colNumber = selectResult.getMetaData().getColumnCount();
                ArrayList<DataResult> rows = new ArrayList(); //This may be faster than a Vector. I hope.
                DataResult columns = new DataResult();
                while (selectResult.next()) {
                    for (int readIteration = 0; readIteration < colNumber; readIteration++) {
                        //you should check for null results.
                        switch (dataTypes[readIteration]) {
                            case Table.INTEGER:
                                columns.addInt(selectResult.getInt(colNumber));
                                break;
                            case Table.STRING:
                                columns.addString(selectResult.getString(colNumber));
                                break;
                            case Table.BLOB:
                                columns.addBlob(selectResult.getBlob(colNumber));
                                break;
                            case Table.DOUBLE:
                                columns.addDouble(selectResult.getDouble(colNumber));
                                break;
                        }
                    }
                    rows.add(columns);
                    result = (DataResult[]) rows.toArray();
                }
            } else if (!skel.toLowerCase().startsWith("select")) {

                int fieldIndex = 1;
                for (String fieldValue : fields) {
                    prepStatement.setString(fieldIndex, fieldValue);
                    fieldIndex++;
                }
                prepStatement.addBatch();

                int[] res = prepStatement.executeBatch();
                result = new DataResult[res.length];
                this.closeConnection();
                for (int x = 0; x < res.length; x++) {
                    result[x] = new DataResult();
                    result[x].addInt(res[x]);
                }
            } else {
                throw new QueryException();
            }
        } catch (SQLException e) {
            //Whoohoo!!! A bug!
            throw new QueryException(e.getSQLState() + "\n" + e.getMessage());
        }
        System.gc();
        return result;
    }

    //This way we tell the Manager we're SQLITE3 (Zentus).
    public String getDatabaseType() {
        return DatabaseFactory.SQLITE3;
    }
}
